package com.cellulam.framework.scaffold.generate.db;

import com.cellulam.framework.scaffold.generate.utils.CamelCaseUtils;
import com.google.common.collect.Lists;
import org.apache.commons.lang3.ClassUtils;
import org.apache.ibatis.type.JdbcType;
import org.mybatis.generator.internal.ObjectFactory;

import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.*;
import java.util.Date;


public class ConnHelper {

    private static Map<String, Driver> loadedDrivers = new HashMap<>();

    public static final ConnHelper instance = new ConnHelper();

    private static String LOADED_DRIVER_CLASS = null;

    private static Driver loadDriver(String driverClass) {
        if (loadedDrivers.containsKey(driverClass)) {
            return loadedDrivers.get(driverClass);
        }

        Driver driver;
        try {
            Class<?> clazz = ObjectFactory.externalClassForName(driverClass);
            driver = (Driver) clazz.newInstance();
            loadedDrivers.put(driverClass, driver);
            ConnHelper.LOADED_DRIVER_CLASS = driverClass;
            System.out.println("success load jdbc driver[" + driverClass + "], driver is " + driver);
        } catch (Exception e) {
            throw new RuntimeException("getDriver error.", e); //$NON-NLS-1$
        }

        return driver;
    }

    public Connection getConnection(String dbName) throws SQLException {
        JdbcConfig jdbcProp = JdbcConfig.get(dbName);

        Driver driver = loadDriver(jdbcProp.getJdbcDriver());

        Properties props = new Properties();
        props.setProperty("user", jdbcProp.getUser());
        props.setProperty("password", jdbcProp.getPassword());

        String jdbcUrl = jdbcProp.getJdbcUrl();

        Connection conn = driver.connect(jdbcUrl, props);

        if (conn == null) {
            throw new SQLException("conn is null"); //$NON-NLS-1$
        }

        return conn;
    }

    public String getPkColumnName(Connection conn, String dbName, String tableName) throws SQLException {
        DatabaseMetaData dbmd = conn.getMetaData();

        // ResultSet rs = dbmd.getPrimaryKeys("AdventureWorks", "Person", "Contact");
        /*
         how to use the getPrimaryKeys method to return information about
         the primary keys of the Person.Contact table in the AdventureWorks service-archetype database.

         String catalog = tables.getString("TABLE_CAT");
        String schema = tables.getString("TABLE_SCHEM");
        String tableName = tables.getString("TABLE_NAME");


         */

        String catalog = null;
        String schema = null;

//        ResultSet tableRs = dbmd.getTables(null, null, "%", new String[] { tableName });
//        if (tableRs.next()) {
//
//        } else {
//            throw new RuntimeException("the table[" + tableName + "] DO NOT exist");
//        }
//        catalog = tableRs.getString("TABLE_CAT");
//        schema = tableRs.getString("TABLE_SCHEM");
//        GenUtils.info("catalog is " + catalog + ", schema is " + schema + " for table[" + tableName + "", "");
//        tableRs.close();

        ResultSet pkrs = dbmd.getPrimaryKeys(catalog, schema, tableName);

        String pkColumnName = null;
        while (pkrs.next()) {
            pkColumnName = pkrs.getString("COLUMN_NAME");
        }
//        else {
//            GenUtils.info("no pk", "\t\t");
//            // throw new RuntimeException("table[" + tableName + "] DO NOT has any pk");
//        }
        pkrs.close();

        return pkColumnName;
    }

    public TableMetaInfo buildTableInfo(String extraPackage, String dbName, String tableName) throws SQLException {
        return buildTableInfo(extraPackage, dbName, tableName, false, false);
    }

    public TableMetaInfo buildTableInfo(String extraPackage, String dbName, String tableName, boolean enableLocalDateTime, boolean enablePrimitiveType) throws SQLException {
        TableMetaInfo tbl = new TableMetaInfo();
        tbl.setTableName(tableName);

        tbl.setColumns(getColumnsOfTable(extraPackage, tbl.getBeanName(), this.getConnection(dbName), dbName, tableName, enableLocalDateTime, enablePrimitiveType));

        String pkColumnName = getPkColumnName(getConnection(dbName), dbName, tableName);
        ColumnField secondPk = tbl.getColumns().get(0);
        tbl.getColumns().forEach(col -> {

            if (col.getFieldType() == null) {
                tbl.addImportedStr(extraPackage + "." + col.getFieldTypeStr());
            } else if (!ClassUtils.isPrimitiveOrWrapper(col.getFieldType())
                    && !col.getFieldType().getName().startsWith("java.lang")) {
                tbl.addImported(col.getFieldType());
            }

            if (pkColumnName != null
                    && col.getColumnName().equals(pkColumnName)) {
                tbl.setPk(col);
                col.setPk(true);
                // 对主键类型强制使用 wrapperType
                if (col.getFieldType().isPrimitive()) {
                    col.setFieldType(ClassUtils.primitiveToWrapper(col.getFieldType()));
                }
            }

        });
        if (pkColumnName == null) {
            tbl.setPk(secondPk);
        }
        List<ColumnField> uniqIndexFields = Lists.newArrayList();

        Connection connection = this.getConnection(dbName);

        DatabaseMetaData dbmd = connection.getMetaData();
        ResultSet indexInfo = dbmd.getIndexInfo(null, null, tableName, true, false);
        while (indexInfo.next()) {
            boolean nonUnique = indexInfo.getBoolean("NON_UNIQUE");
            String columnName = indexInfo.getString("COLUMN_NAME");
            if (!nonUnique && !columnName.equals(tbl.getPk().getColumnName())) {
                uniqIndexFields.add(tbl.getColumns().stream()
                        .filter(x -> x.getColumnName().equals(columnName)).findFirst().orElse(null));
            }
        }
        connection.close();
        tbl.setUniqKeyFields(uniqIndexFields);

        return tbl;
    }

    private boolean isSQLServer() {
        return LOADED_DRIVER_CLASS != null
                && LOADED_DRIVER_CLASS.contains("sqlserver");
    }

    public List<ColumnField> getColumnsOfTable(String extraPackage, String beanName, Connection conn, String dbName, String tableName,
                                               boolean enableLocalDateTime, boolean enablePrimitiveType) throws SQLException {

        List<ColumnField> columns = new ArrayList<>();

        Map<String, String> columnRemarks = new HashMap<>();
        // com.microsoft.sqlserver.jdbc.SQLServerDriver
        if (isSQLServer()) {
            columnRemarks = getColumnRemarks4mssql(conn, tableName);
            ;
        }

        DatabaseMetaData dbmd = conn.getMetaData();
        ResultSet rs = dbmd.getColumns(null, null, tableName, null);

        while (rs.next()) {
            ColumnField field = new ColumnField();// colName, CamelCaseUtils.toCamelCase(colName), colType.getSimpleName()
            field.setColumnName(rs.getString("COLUMN_NAME"));
            field.setFieldName(CamelCaseUtils.toCamelCase(field.getColumnName()));
            if (isSQLServer()) {
                field.setComment(columnRemarks.get(field.getColumnName()));
            } else {
                field.setComment(rs.getString("REMARKS"));
            }

            field.setDefaultValue(rs.getString("COLUMN_DEF"));

            field.setAllowNull(rs.getInt("NULLABLE") == 1);

            field.setAuto("YES".equals(rs.getString("IS_AUTOINCREMENT")));

//            String jdbcType = rs.getString("TYPE_NAME");
            int jdbcType = rs.getInt("DATA_TYPE");
            String typeName = rs.getString("TYPE_NAME");

            if ("json".equalsIgnoreCase(typeName)) {
                System.out.println("\t\tfield#" + field.getColumnName() + "'s type is " + typeName);
                field.setColumnType(typeName);
                field.setFieldTypeStr(beanName + CamelCaseUtils.toCapitalizeCamelCase(field.getFieldName()) + "Model");
                field.setJavaTypeText(extraPackage + "." + field.getFieldTypeStr());
                columns.add(field);
            } else {
                field.setColumnType(getJdbcTypeName(jdbcType));
                Class<?> colType = getJavaType(jdbcType, typeName, enableLocalDateTime, field.isAllowNull(), enablePrimitiveType);
                if (colType != null) {
                    field.setFieldType(colType);
                    columns.add(field);
                } else {
                    throw new RuntimeException("table[" + tableName + "]'s column[" + field.getColumnName() + "]'s type is null");
                }
            }

        }
        rs.close();

        return columns;
    }

    static String mssql_remarks_get_sql = "SELECT objname, cast(value as varchar(128)) as value " +
            " FROM fn_listextendedproperty ('MS_DESCRIPTION','schema', 'dbo', 'table', '{table_name}', 'column', null)";

    private Map<String, String> getColumnRemarks4mssql(Connection conn, String tableName) {
        Map<String, String> columnRemarks = new HashMap<>();
        String sql = mssql_remarks_get_sql.replaceAll("\\{table_name}", tableName);
        try {
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            while (rs.next()) {
                columnRemarks.put(rs.getString(1), rs.getString(2));
            }
            rs.close();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return columnRemarks;
    }

    private String getJdbcTypeName(int jdbcType) {
        return jdbcTypeMap.get(jdbcType).toString();
    }

    private Class<?> getJavaType(int jdbcType, String typeName, boolean enableLocalDateTime,
                                 boolean allowNull, boolean enablePrimitiveType) {
        Class<?> javaType = null;
        if (enableLocalDateTime) {
            javaType = processLocalDateType(typeName);
        }
        if (javaType == null) {
            javaType = javaTypeMap.get(jdbcType);
            if (!allowNull && ClassUtils.isPrimitiveWrapper(javaType) && enablePrimitiveType) {
                javaType = ClassUtils.wrapperToPrimitive(javaType);
            }
        }

        return javaType;
    }

    private Class<?> processLocalDateType(String typeName) {
        if ("datetime".equalsIgnoreCase(typeName)) {
            return LocalDateTime.class;
        } else if ("date".equalsIgnoreCase(typeName)) {
            return LocalDate.class;
        } else if ("time".equalsIgnoreCase(typeName)) {
            return LocalTime.class;
        } else if ("timestamp".equalsIgnoreCase(typeName)) {
            return LocalDateTime.class;
        }
        return null;
    }

    static Map<Integer, Class<?>> javaTypeMap = new HashMap<>();
    static Map<Integer, JdbcType> jdbcTypeMap = new HashMap<>();

    static {
        javaTypeMap.put(Types.DATE, Date.class);
        javaTypeMap.put(Types.TIME, Date.class);
        javaTypeMap.put(Types.TIMESTAMP, Date.class);

        javaTypeMap.put(Types.ARRAY, List.class);
        javaTypeMap.put(Types.BIGINT, Long.class);
        javaTypeMap.put(Types.BINARY, byte[].class); //$NON-NLS-1$
        javaTypeMap.put(Types.BIT, Boolean.class);
        javaTypeMap.put(Types.BLOB, byte[].class); //$NON-NLS-1$
        javaTypeMap.put(Types.BOOLEAN, Boolean.class);
        javaTypeMap.put(Types.CHAR, String.class);
        javaTypeMap.put(Types.CLOB, String.class);
        javaTypeMap.put(Types.DATALINK, Object.class);

        javaTypeMap.put(Types.DECIMAL, BigDecimal.class);
        javaTypeMap.put(Types.DISTINCT, Object.class);
        javaTypeMap.put(Types.DOUBLE, Double.class);
        javaTypeMap.put(Types.FLOAT, Float.class);
        javaTypeMap.put(Types.INTEGER, Integer.class);
        javaTypeMap.put(Types.JAVA_OBJECT, Object.class);
        javaTypeMap.put(Types.LONGNVARCHAR, String.class);
        javaTypeMap.put(Types.LONGVARBINARY, byte[].class);
        javaTypeMap.put(Types.LONGVARCHAR, String.class);
        javaTypeMap.put(Types.NCHAR, String.class);
        javaTypeMap.put(Types.NCLOB, String.class);
        javaTypeMap.put(Types.NVARCHAR, String.class);
        javaTypeMap.put(Types.NULL, Object.class);
        javaTypeMap.put(Types.NUMERIC, BigDecimal.class);
        javaTypeMap.put(Types.OTHER, Object.class);
        javaTypeMap.put(Types.REAL, Double.class);
        javaTypeMap.put(Types.REF, Object.class);
        javaTypeMap.put(Types.SMALLINT, Integer.class);
        javaTypeMap.put(Types.STRUCT, Object.class);

        javaTypeMap.put(Types.TINYINT, Integer.class);
        javaTypeMap.put(Types.VARBINARY, byte[].class);
        javaTypeMap.put(Types.VARCHAR, String.class);

        //
        jdbcTypeMap.put(Types.DATE, JdbcType.DATE);
        jdbcTypeMap.put(Types.TIME, JdbcType.TIME);
        jdbcTypeMap.put(Types.TIMESTAMP, JdbcType.TIMESTAMP);

        jdbcTypeMap.put(Types.ARRAY, JdbcType.ARRAY);
        jdbcTypeMap.put(Types.BIGINT, JdbcType.BIGINT);
        jdbcTypeMap.put(Types.BINARY, JdbcType.BINARY); //$NON-NLS-1$
        jdbcTypeMap.put(Types.BIT, JdbcType.BIT);
        jdbcTypeMap.put(Types.BLOB, JdbcType.BLOB); //$NON-NLS-1$
        jdbcTypeMap.put(Types.BOOLEAN, JdbcType.BOOLEAN);
        jdbcTypeMap.put(Types.CHAR, JdbcType.CHAR);
        jdbcTypeMap.put(Types.CLOB, JdbcType.CLOB);
        jdbcTypeMap.put(Types.DATALINK, JdbcType.DATALINK);

        jdbcTypeMap.put(Types.DECIMAL, JdbcType.DECIMAL);
        jdbcTypeMap.put(Types.DISTINCT, JdbcType.DISTINCT);
        jdbcTypeMap.put(Types.DOUBLE, JdbcType.DOUBLE);
        jdbcTypeMap.put(Types.FLOAT, JdbcType.FLOAT);
        jdbcTypeMap.put(Types.INTEGER, JdbcType.INTEGER);
        jdbcTypeMap.put(Types.JAVA_OBJECT, JdbcType.JAVA_OBJECT);
        jdbcTypeMap.put(Types.LONGNVARCHAR, JdbcType.LONGNVARCHAR);
        jdbcTypeMap.put(Types.LONGVARBINARY, JdbcType.LONGVARBINARY);
        jdbcTypeMap.put(Types.LONGVARCHAR, JdbcType.LONGVARCHAR);
        jdbcTypeMap.put(Types.NCHAR, JdbcType.NCHAR);
        jdbcTypeMap.put(Types.NCLOB, JdbcType.NCLOB);
        jdbcTypeMap.put(Types.NVARCHAR, JdbcType.NVARCHAR);
        jdbcTypeMap.put(Types.NULL, JdbcType.NULL);
        jdbcTypeMap.put(Types.NUMERIC, JdbcType.NUMERIC);
        jdbcTypeMap.put(Types.OTHER, JdbcType.OTHER);
        jdbcTypeMap.put(Types.REAL, JdbcType.REAL);
        jdbcTypeMap.put(Types.REF, JdbcType.REF);
        jdbcTypeMap.put(Types.SMALLINT, JdbcType.SMALLINT);
        jdbcTypeMap.put(Types.STRUCT, JdbcType.STRUCT);

        jdbcTypeMap.put(Types.TINYINT, JdbcType.TINYINT);
        jdbcTypeMap.put(Types.VARBINARY, JdbcType.VARBINARY);
        jdbcTypeMap.put(Types.VARCHAR, JdbcType.VARCHAR);
    }


}
